package com.blogspot.wanyancan.server;

import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;



import org.apache.log4j.Logger;

import com.blogspot.wanyancan.client.image.ImageRec;
import com.blogspot.wanyancan.client.image.ImageService;
import com.google.gwt.user.server.rpc.RemoteServiceServlet;

public class ImageServiceImpl extends RemoteServiceServlet implements
		ImageService {

	static Logger logger = Logger.getLogger(ImageServiceImpl.class);

	public static final String[] PROPLIST = new String[] { "None", "MB", "AL",
			"PN", "Calyx", "Ubiquitous", "other 1", "other 2", "other 3",
			"other 4", "other 5" };

	static Connection conn;

	public ImageServiceImpl() {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(
					"jdbc:mysql://localhost/imagedb", "imageuser", "111111");
		} catch (Exception ex) {
			logger.error(ex.getLocalizedMessage());
		}
	}

	/* private LobHandler lobHandler; */

	/**
	 * Set the LobHandler to use for BLOB/CLOB access. Could use a
	 * DefaultLobHandler instance as default, but relies on a specified
	 * LobHandler here.
	 * 
	 * @see org.springframework.jdbc.support.lob.DefaultLobHandler
	 */
	/*
	 * public void setLobHandler(LobHandler lobHandler) { this.lobHandler =
	 * lobHandler; }
	 */
	public ImageRec getImage(String id) {
		return getImage(id, "imagedb");
	}

	Statement stat;

	ResultSet rs;

	/**
	 * Get single ImageRec by id
	 * @see com.blogspot.wanyancan.client.image.ImageService#getImage(java.lang.String, java.lang.String)
	 */
	public ImageRec getImage(String id, String db) {
		ImageRec retRec = null;

		try {
			stat = conn.createStatement();
			rs = stat.executeQuery("SELECT id,image_name,description FROM "
					+ db + " WHERE id='" + id+"'");
			while (rs.next()) {
				retRec = new ImageRec();
				retRec.setId(rs.getInt(1));
				retRec.setName(rs.getString(2));
				retRec.setDescription(rs.getString(3));
				break;
			}
		} catch (Exception ex) {
			logger.error(ex);
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException sqlEx) {
				}// do nothing
				rs = null;
			}

			if (stat != null) {
				try {
					stat.close();
				} catch (SQLException sqlEx) {
				} // do nothing
				stat = null;
			}
		}
		return retRec;
	}

	static final ImageRec[] NO_IMAGES = new ImageRec[0];

	public ImageRec[] getImages(String db) {
		ImageRec[] retImageList = NO_IMAGES;
		List<ImageRec> resList = new ArrayList<ImageRec>();
		long timestart = System.currentTimeMillis();
		try {
			stat = conn.createStatement();
			rs = stat.executeQuery("SELECT image_name,description FROM " + db
					+ " GROUP BY image_name");
			ImageRec thisimg;
			while (rs.next()) {
				thisimg = new ImageRec();
				thisimg.setName(rs.getString(1));
				thisimg.setDescription(rs.getString(2));
				Statement stat_sub1 = conn.createStatement();
				ResultSet rs_sub1 = stat_sub1.executeQuery(String.format(
						"SELECT prop FROM imageclass WHERE image_name=\"%s\"",
						thisimg.getName()));
				while (rs_sub1.next()) {
					thisimg.setProp(rs_sub1.getString(1));
					break;
				}
				resList.add(thisimg);
				thisimg = null;
			}

			// second step, full fill img list under the same name
			rs = null;
			Iterator<ImageRec> it = resList.iterator();
			while (it.hasNext()) {
				ImageRec ob = it.next();
				String thisname = ob.getName();
				final StringBuilder thisdesc = new StringBuilder();
				final List<Integer> speimglist = new ArrayList<Integer>();
				rs = stat
						.executeQuery(String
								.format(
										"SELECT id,description FROM %s WHERE image_name=\"%s\"",
										db, thisname));
				while (rs.next()) {
					speimglist.add(rs.getInt(1));
					thisdesc.append(" " + rs.getString(2));
				}
				ob.setImglist(speimglist);
				ob.setDescription(thisdesc.toString());
			}
			retImageList = resList.toArray(NO_IMAGES);
		} catch (Exception ex) {
			logger.error(ex.getMessage());
			return retImageList;
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (final SQLException sqlEx) {
				}// do nothing
				rs = null;
			}

			if (stat != null) {
				try {
					stat.close();
				} catch (final SQLException sqlEx) {
				} // do nothing
				stat = null;
			}
			logger.info("getImages called , time used(ms): "
					+ (System.currentTimeMillis() - timestart));
		}
		return retImageList ;
	}

	public ImageRec[] getImages() {
		return getImages("imagedb");
	}

	public void streamSImage(final int id, final String name,
			final OutputStream contentStream, final String db) throws Exception {
		// TODO: not implemented yet
		return;
		/*
		 * getJdbcTemplate().query( "SELECT small_ct FROM " + db + " WHERE id=?
		 * and image_name=?", new Object[] { id, name }, new
		 * AbstractLobStreamingResultSetExtractor() { protected void
		 * handleNoRowFound() throws LobRetrievalFailureException { throw new
		 * IncorrectResultSizeDataAccessException( "Image with id '" + id + "', " +
		 * "name '" + name + "' not found in database " + db, 1, 0); }
		 * 
		 * public void streamData(ResultSet rs) throws SQLException {
		 * InputStream is = lobHandler .getBlobAsBinaryStream(rs, 1); if (is !=
		 * null) { try { FileCopyUtils.copy(is, contentStream); } catch
		 * (IOException e) { logger.info("may be this record with id '" + id +
		 * "', " + "name '" + name + "' is not a image file"); } } } });
		 */
	}

	public void streamSImage(final int id, final String name,
			final OutputStream contentStream) throws Exception {
		streamSImage(id, name, contentStream, "imagedb");
	}

	public void streamImage(final int id, final String name,
			final OutputStream contentStream, final String db) throws Exception {
		// TODO: Not implemented yet
		return;
		/*
		 * getJdbcTemplate().query( "SELECT content FROM " + db + " WHERE id=?
		 * and image_name=?", new Object[] { id, name }, new
		 * AbstractLobStreamingResultSetExtractor() { protected void
		 * handleNoRowFound() throws LobRetrievalFailureException { throw new
		 * IncorrectResultSizeDataAccessException( "Image with id '" + id + "', " +
		 * "name '" + name + "' not found in database " + db, 1, 0); }
		 * 
		 * public void streamData(ResultSet rs) throws SQLException {
		 * InputStream is = lobHandler .getBlobAsBinaryStream(rs, 1); if (is !=
		 * null) { try { FileCopyUtils.copy(is, contentStream); } catch
		 * (IOException e) { logger.info("may be this record with id '" + id +
		 * "', " + "name '" + name + "' is not a image file"); } } } });
		 */}

	public void streamImage(final int id, final String name,
			final OutputStream contentStream) throws Exception {
		streamImage(id, name, contentStream, "imagedb");
	}

	// store to specified 'db'
	public void storeImage(final String name, final InputStream contentStream,
			final int contentLength, final InputStream smos, final int smosz,
			final String description, String db) {
		// TODO: not implemented yet
		/*
		 * getJdbcTemplate() .execute( "INSERT INTO " + db + " (image_name,
		 * content, description, small_ct) VALUES (?, ?, ?, ?)", new
		 * AbstractLobCreatingPreparedStatementCallback( this.lobHandler) {
		 * protected void setValues(PreparedStatement ps, LobCreator lobCreator)
		 * throws SQLException { ps.setString(1, name);
		 * lobCreator.setBlobAsBinaryStream(ps, 2, contentStream,
		 * contentLength); lobCreator.setClobAsString(ps, 3, description);
		 * lobCreator.setBlobAsBinaryStream(ps, 4, smos, smosz); } });
		 */
	}

	// store to default 'imagedb';
	public void storeImage(final String name, final InputStream contentStream,
			final int contentLength, final InputStream smos, final int smosz,
			final String description) {
		storeImage(name, contentStream, contentLength, smos, smosz,
				description, "imagedb");
	}

	public void checkImages() {
		// Could implement consistency check here...
		logger
				.info("Checking images: not implemented but invoked by scheduling");
	}

	public void UpdateImageId(String id, String name, String desc) {
		UpdateImageId(id, name, desc, "imagedb");
	}

	public void UpdateImageId(final String id, final String name,
			final String desc, String db) {
		try {
			stat = conn.createStatement();
			int numAffected = stat.executeUpdate(
					String.format("UPDATE '%s' SET image_name=\"%s\" , description=\"%s\" WHERE id='%s'", 
							db, name, desc, id));
			logger.debug(String.format("%d records updated", numAffected));
		} catch (Exception ex) {
			logger.error(ex);
		} finally {
			if (stat != null) {
				try {
					stat.close();
				} catch (SQLException sqlEx) {
				} // do nothing
				stat = null;
			}
		}
		/*
		 * getJdbcTemplate() .execute( "UPDATE " + db + " SET image_name=? ,
		 * description=? WHERE id=?", new
		 * AbstractLobCreatingPreparedStatementCallback( this.lobHandler) {
		 * @Override protected void setValues(PreparedStatement ps, LobCreator
		 * lobCreator) throws SQLException, DataAccessException {
		 * ps.setString(1, name); lobCreator.setClobAsString(ps, 2, desc);
		 * ps.setInt(3, (Integer.parseInt(id))); } });
		 */
	}

	public void clearDatabase(String db) {
		try {
			stat = conn.createStatement();
			int numAffected = stat.executeUpdate("DELETE FROM " + db);
			logger.warn(String.format(
					"Database %s cleared, affecting %d records", db,
					numAffected));
		} catch (Exception ex) {
			logger.error(ex);
		} finally {
			if (stat != null) {
				try {
					stat.close();
				} catch (SQLException sqlEx) {
				} // do nothing
				stat = null;
			}
		}
	}

	public void clearDatabase() {
		clearDatabase("imagedb");
	}

	public void updateImgClass(String name, String prop) {
		updateImgClass(name, prop, "imageclass");
	}

	public void updateImgClass(final String name, final String prop, String db) {
		try {
			
			stat = conn.createStatement();

			int numAffected = 0;
			try {
				numAffected = stat.executeUpdate(String.format(
						"INSERT INTO %s (image_name, prop) VALUES (\"%s\", \"%s\")",
						db, name, prop));
			} catch (Exception e) {
				numAffected = stat.executeUpdate(String.format(
						"UPDATE %s SET prop='%s' WHERE image_name='%s'", db, prop,
						name));
			}
			bAllImabeModified = true;
			logger.debug(String.format(
					"%d records's prop got updated, database: %s", numAffected,
					db));
		} catch (Exception ex) {
			logger.error(ex);
		} finally {
			if (stat != null) {
				try {
					stat.close();
				} catch (SQLException sqlEx) {
				} // do nothing
				stat = null;
			}
		}

	}

	public void deleteImage(String name, String db) {
		try {
			stat = conn.createStatement();
			int numAffected = stat.executeUpdate(String.format(
					"DELETE FROM %s WHERE image_name=\"%s\"", db, name));
			logger.warn(String.format(
					"Database %s with name %s deleted, affecting %d records",
					db, name, numAffected));
		} catch (Exception ex) {
			logger.error(ex);
		} finally {
			if (stat != null) {
				try {
					stat.close();
				} catch (SQLException sqlEx) {
				} // do nothing
				stat = null;
			}
		}
	}

	public void deleteImage(String name) {
		deleteImage(name, "imagedb");
	}

	public void checkImages(String db) {
		logger
				.info("Checking images on specialdb: not implemented but invoked by scheduling");
	}

	public void deleteImageId(String id, String name) {
		deleteImageId(id, name, "imagedb");
	}

	public void deleteImageId(String id, String name, String db) {
		try {
			stat = conn.createStatement();
			int numAffected = stat.executeUpdate(String.format(
					"DELETE FROM %s WHERE image_name=\"%s\" and id='%s'", db,
					name, id));
			logger
					.warn(String
							.format(
									"Database %s with name %s AND id %s deleted, affecting %d records",
									db, name, id, numAffected));
		} catch (Exception ex) {
			logger.error(ex);
		} finally {
			if (stat != null) {
				try {
					stat.close();
				} catch (SQLException sqlEx) {
				} // do nothing
				stat = null;
			}
		}
	}
	boolean bAllImabeModified = false;
	ImageRec[] lastAllList ;
	public ImageRec[] getImage(int startIndex, int numCount) {
		ImageRec[] allList = lastAllList;
		if(bAllImabeModified || lastAllList == null){
			allList = lastAllList = getImages();
			bAllImabeModified = false;
		}
		if(startIndex<0) startIndex = 0;
		if(startIndex>=allList.length) return NO_IMAGES;
		if(startIndex+numCount>allList.length) numCount = allList.length-startIndex;

		ImageRec[] retList = new ImageRec[numCount];
		
		for(int i = startIndex; i<startIndex+numCount; i++){
			retList[i-startIndex] = allList[i];
		}
		return retList;
	}
}
